Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections
Imports System.Collections.Generic
Imports Microsoft.VisualBasic

Namespace MB.PsychCal.DAL.SqlClient
    Public Class SqlArticlesProvider
        Inherits ArticlesProvider


        Public Overrides Function GetArticleBody(ByVal articleID As Integer) As String
            Using cn As New SqlConnection(Me.ConnectionString)
                Dim cmd As New SqlCommand("rb_Articles_GetArticleBody", cn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add("@ArticleID", SqlDbType.Int).Value = articleID
                cn.Open()
                Return CStr(executeScalar(cmd))
            End Using
        End Function

        Public Overrides Function GetArticleByID(ByVal articleID As Integer) As ArticleDetails
            Using cn As New SqlConnection(Me.ConnectionString)
                Dim cmd As New SqlCommand("rb_Articles_GetArticleByID", cn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add("@ArticleID", SqlDbType.Int).Value = articleID
                cn.Open()
                Dim reader As IDataReader = ExecuteReader(cmd, CommandBehavior.SingleRow)
                If reader.Read() Then
                    Return GetArticleFromReader(reader, True)
                Else
                    Return Nothing
                End If
            End Using
        End Function

        Public Overloads Overrides Function GetArticleCount() As Integer
            Using cn As New SqlConnection(Me.ConnectionString)
                Dim cmd As New SqlCommand("Announcement_GetArticleCount", cn)
                cmd.CommandType = CommandType.StoredProcedure
                cn.Open()
                Return CInt(executeScalar(cmd))
            End Using
        End Function


        Public Overloads Overrides Function GetArticles(ByVal pageIndex As Integer, ByVal pageSize As Integer) As System.Collections.Generic.List(Of ArticleDetails)
            Using cn As New SqlConnection(Me.ConnectionString)
                Dim cmd As New SqlCommand("Announcements_GetAllAnnouncements", cn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex
                cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize
                cn.Open()
                Return GetArticleCollectionFromReader(ExecuteReader(cmd), False)
            End Using
        End Function

        Public Function IncrementArticleViewCount(ByVal articleID As Integer) As Boolean
            Using cn As New SqlConnection(Me.ConnectionString)
                Dim cmd As New SqlCommand("rb_Articles_IncrementViewCount", cn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add("@ArticleID", SqlDbType.Int).Value = articleID
                cn.Open()
                Dim ret As Integer = ExecuteNonQuery(cmd)
                Return (ret = 1)
            End Using
        End Function

        Public Overrides Function InsertArticle(ByVal article As ArticleDetails) As Integer
            Using cn As New SqlConnection(Me.ConnectionString)
                Dim cmd As New SqlCommand("Announcement_InsertAnnouncement", cn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add("@madeby", SqlDbType.VarChar).Value = article.madeby
                cmd.Parameters.Add("@subject", SqlDbType.VarChar).Value = article.subject
                cmd.Parameters.Add("@message", SqlDbType.VarChar).Value = article.message
                cmd.Parameters.Add("@addeddate", SqlDbType.DateTime).Value = article.AddedDate
                cmd.Parameters.Add("@isexpired", SqlDbType.Bit).Value = article.isexpired
                cmd.Parameters.Add("@announcementid", SqlDbType.Int).Direction = ParameterDirection.Output

                cn.Open()
                Dim ret As Integer = ExecuteNonQuery(cmd)
                Return CInt(cmd.Parameters("@announcementID").Value)
            End Using
        End Function

    End Class
End Namespace
